import openpyxl
import SplitWord
from openpyxl import Workbook
import re
import time
import threading


def get_ymd(str_ymd):
    pattern_ymd = re.compile(r'\d+年\d+月\d+日')  # 定义匹配模式
    list = re.findall(pattern_ymd, str_ymd)
    if list.__len__() > 0:
        return list[0]
    else:
        pattern_ym = re.compile(r'\d+年\d+月')
        list = re.findall(pattern_ym, str_ymd)
        if list.__len__() > 0:
            return list[0]
        else:
            pattern_y = re.compile(r'\d+年')
            list = re.findall(pattern_y, str_ymd)
            if list.__len__() > 0:
                return list[0]
            else:
                return ''


def format_time(str_time):
    if str_time == 0:
        return ''
    my_time = ''
    if str_time.find('（'):
        my_time = str_time.split('（')[0]
    else:
        my_time = str_time
    try:
        if my_time.find('日') != -1:
            array = time.strptime(my_time, u"%Y年%m月%d日")
            new_time = time.strftime("%Y-%m-%d", array)
            return new_time
        if my_time.find('月') != -1:
            array = time.strptime(my_time, u"%Y年%m月")
            new_time = time.strftime("%Y-%m", array)
            return new_time
        if my_time.find('年') != -1:
            array = time.strptime(my_time, u"%Y年")
            new_time = time.strftime("%Y", array)
            return new_time
    except Exception as e:
        print(e)
        return ''


def loc_and_time_deal(path):
    wb = openpyxl.load_workbook(path)
    # 获取所有工作表名
    names = wb.sheetnames
    # wb.get_sheet_by_name(name) 已经废弃,使用wb[name] 获取指定工作表
    sheet = wb[names[0]]
    # 获取最大行数
    # maxRow = sheet.max_row
    # 获取最大列数
    # maxColumn = sheet.max_column
    # 获取当前活动表
    # current_sheet = wb.active
    # 获取当前活动表名称
    # current_name = sheet.title
    # 通过名字访问Cell对象, 通过value属性获取值
    # a1 = sheet['A1'].value
    # 通过行和列确定数据
    # a12 = sheet.cell(row=1, column=2).value
    # 获取列字母
    # column_name = openpyxl.utils.cell.get_column_letter(1)
    # 将列字母转为数字, 参数忽略大小写
    # column_name_num = openpyxl.utils.cell.column_index_from_string('a')
    # 获取一列数据, sheet.iter_rows() 获取所有的行
    """
    (<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>)
    (<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>)
    (<Cell 'Sheet1'.A3>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.C3>)
    (<Cell 'Sheet1'.A4>, <Cell 'Sheet1'.B4>, <Cell 'Sheet1'.C4>)
    (<Cell 'Sheet1'.A5>, <Cell 'Sheet1'.B5>, <Cell 'Sheet1'.C5>)
    """
    # for one_column_data in sheet.iter_rows():
    #     print(one_column_data[0].value)

    # 获取一行数据, sheet.iter_cols() 获取所有的列
    """
    (<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.A2>, <Cell 'Sheet1'.A3>)
    (<Cell 'Sheet1'.B1>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.B3>)
    (<Cell 'Sheet1'.C1>, <Cell 'Sheet1'.C2>, <Cell 'Sheet1'.C3>)
    """
    cols = sheet.iter_rows()
    ws = wb.active
    threads = []
    for i in range(5):
        t = threading.Thread(target=deal_data, args=(cols, ws,))
        threads.append(t)
        t.start()

    wb.save("文件" + str(time.time()) + '.xlsx')

def deal_data(cols, ws):
    for col in cols:
        with threading.Lock():
            if col[0].value == 'location':
                try:
                    loc_name = SplitWord.get_loc_name(col[19].value)
                    loc_time = format_time(get_ymd(col[19].value))
                    ws.cell(row=col[0].row, column=15).value = loc_name
                    ws.cell(row=col[0].row, column=14).value = loc_time
                except Exception as e:
                    print(e)
                    continue



loc_and_time_deal('测试1.xlsx')
# paths = [['文件1.xlsx'],['文件2.xlsx'],['文件3.xlsx'],['文件4.xlsx'],['文件5.xlsx']]
#
# threads = []
# for i in range(5):
#     t = threading.Thread(target=loc_and_time_deal, args=(paths[i]))
#     threads.append(t)
#     t.start()
